package com.datatrans.service;

import com.datatrans.config.TransferConfig;
import com.datatrans.exception.TransferException;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Set;

/**
 * 数据迁移服务类
 */
public class DataTransferService {
    
    private DatabaseService databaseService = new DatabaseService();
    
    /**
     * 执行数据迁移
     */
    public void transfer(TransferConfig config) throws TransferException {
        Connection sourceConn = null;
        Connection targetConn = null;
        
        try {
            // 获取数据库连接
            sourceConn = databaseService.getSourceConnection(config);
            targetConn = databaseService.getTargetConnection(config);
            
            // 执行迁移
            doTransfer(sourceConn, targetConn, config);
        } finally {
            // 关闭连接
            closeConnection(sourceConn);
            closeConnection(targetConn);
        }
    }
    
    /**
     * 执行实际的数据迁移操作
     */
    private void doTransfer(Connection sourceConn, Connection targetConn, TransferConfig config) 
            throws TransferException {
        try {
            // 获取表映射列表
            List<TransferConfig.TableMapping> tableMappings = getTableMappings(config);
            
            // 遍历所有表映射并执行迁移
            for (TransferConfig.TableMapping mapping : tableMappings) {
                // 获取表的列信息
                List<Column> columns = getColumns(sourceConn, mapping.getSourceTable());
                
                // 根据迁移模式执行不同操作
                switch (config.getTransferMode()) {
                    case DELETE_THEN_COPY:
                        deleteThenCopy(sourceConn, targetConn, config, mapping, columns);
                        break;
                    case UPDATE:
                        update(sourceConn, targetConn, config, mapping, columns);
                        break;
                    case IGNORE:
                        ignore(sourceConn, targetConn, config, mapping, columns);
                        break;
                    default:
                        throw new TransferException("不支持的迁移模式: " + config.getTransferMode());
                }
            }
        } catch (SQLException e) {
            throw new TransferException("数据迁移过程中发生错误: " + e.getMessage(), e);
        }
    }
    
    /**
     * 获取表映射列表
     */
    private List<TransferConfig.TableMapping> getTableMappings(TransferConfig config) {
        List<TransferConfig.TableMapping> tableMappings = config.getTableMappings();
        
        // 如果没有显式设置表映射，则使用单表配置
        if (tableMappings == null || tableMappings.isEmpty()) {
            // 创建一个默认的表映射
            TransferConfig.TableMapping mapping = new TransferConfig.TableMapping();
            mapping.setSourceTable(config.getSourceTable());
            mapping.setTargetTable(config.getTargetTable()); // 如果targetTable为null，则默认等于sourceTable
            
            tableMappings = new ArrayList<>();
            tableMappings.add(mapping);
        }
        
        return tableMappings;
    }
    
    /**
     * 删除后拷贝模式
     */
    private void deleteThenCopy(Connection sourceConn, Connection targetConn, 
                               TransferConfig config, TransferConfig.TableMapping mapping,
                               List<Column> columns) throws SQLException {
        Statement sourceStmt = null;
        Statement targetStmt = null;
        
        try {
            sourceStmt = sourceConn.createStatement();
            targetStmt = targetConn.createStatement();
            
            // 删除目标表数据
            String deleteSql = "DELETE FROM " + mapping.getTargetTable();
            targetStmt.executeUpdate(deleteSql);
            
            // 从源表查询数据
            String selectSql = "SELECT * FROM " + mapping.getSourceTable();
            ResultSet rs = sourceStmt.executeQuery(selectSql);
            
            // 如果没有列信息，直接返回
            if (columns.isEmpty()) {
                rs.close();
                return;
            }
            
            // 构建插入语句
            StringBuilder insertSqlBuilder = new StringBuilder();
            insertSqlBuilder.append("INSERT INTO ").append(mapping.getTargetTable()).append(" (");
            
            // 添加列名（去重）
            List<String> uniqueColumnNames = getUniqueColumnNames(columns);
            for (int i = 0; i < uniqueColumnNames.size(); i++) {
                if (i > 0) insertSqlBuilder.append(", ");
                insertSqlBuilder.append(uniqueColumnNames.get(i));
            }
            
            insertSqlBuilder.append(") VALUES (");
            
            // 添加占位符
            for (int i = 0; i < uniqueColumnNames.size(); i++) {
                if (i > 0) insertSqlBuilder.append(", ");
                insertSqlBuilder.append("?");
            }
            
            insertSqlBuilder.append(")");
            
            String insertSql = insertSqlBuilder.toString();
            PreparedStatement insertStmt = targetConn.prepareStatement(insertSql);
            
            // 插入数据到目标表
            while (rs.next()) {
                for (int i = 0; i < uniqueColumnNames.size(); i++) {
                    String columnName = uniqueColumnNames.get(i);
                    Object value = rs.getObject(columnName);
                    insertStmt.setObject(i + 1, value);
                }
                insertStmt.executeUpdate();
            }
            
            rs.close();
            insertStmt.close();
        } finally {
            if (sourceStmt != null) sourceStmt.close();
            if (targetStmt != null) targetStmt.close();
        }
    }
    
    /**
     * 更新模式
     */
    private void update(Connection sourceConn, Connection targetConn, 
                       TransferConfig config, TransferConfig.TableMapping mapping,
                       List<Column> columns) throws SQLException {
        // 简单实现：先删除后插入
        deleteThenCopy(sourceConn, targetConn, config, mapping, columns);
    }
    
    /**
     * 忽略模式
     */
    private void ignore(Connection sourceConn, Connection targetConn, 
                       TransferConfig config, TransferConfig.TableMapping mapping,
                       List<Column> columns) throws SQLException {
        // 检查目标表是否已存在数据，如果存在则跳过，否则执行拷贝
        Statement targetStmt = null;
        try {
            targetStmt = targetConn.createStatement();
            String countSql = "SELECT COUNT(*) FROM " + mapping.getTargetTable();
            ResultSet rs = targetStmt.executeQuery(countSql);
            rs.next();
            int count = rs.getInt(1);
            rs.close();
            
            if (count == 0) {
                deleteThenCopy(sourceConn, targetConn, config, mapping, columns);
            }
        } finally {
            if (targetStmt != null) targetStmt.close();
        }
    }
    
    /**
     * 获取去重后的列名列表
     */
    private List<String> getUniqueColumnNames(List<Column> columns) {
        Set<String> uniqueNames = new LinkedHashSet<>();
        List<String> result = new ArrayList<>();
        
        for (Column column : columns) {
            String columnName = column.getName();
            if (!uniqueNames.contains(columnName)) {
                uniqueNames.add(columnName);
                result.add(columnName);
            }
        }
        
        return result;
    }
    
    /**
     * 获取表的列信息
     */
    private List<Column> getColumns(Connection conn, String tableName) throws SQLException {
        List<Column> columns = new ArrayList<>();
        DatabaseMetaData metaData = conn.getMetaData();
        
        if (metaData == null) {
            throw new SQLException("无法获取数据库元数据信息");
        }
        
        // 获取表名的实际格式（处理大小写敏感问题）
        String actualTableName = getActualTableName(conn, tableName);
        
        // 首先尝试通过查询语句获取实际的列信息
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM " + actualTableName + " WHERE 1=0");
            java.sql.ResultSetMetaData rsMetaData = rs.getMetaData();
            
            if (rsMetaData != null) {
                int columnCount = rsMetaData.getColumnCount();
                for (int i = 1; i <= columnCount; i++) {
                    Column column = new Column();
                    column.setName(rsMetaData.getColumnName(i));
                    column.setType(rsMetaData.getColumnType(i));
                    column.setTypeName(rsMetaData.getColumnTypeName(i));
                    columns.add(column);
                }
            }
            
            rs.close();
            stmt.close();
            
            // 如果成功获取到列信息，直接返回
            if (!columns.isEmpty()) {
                return columns;
            }
        } catch (SQLException e) {
            // 如果通过查询获取列信息失败，继续使用DatabaseMetaData方式
        }
        
        // 使用DatabaseMetaData获取列信息
        ResultSet columnsRs = metaData.getColumns(null, null, actualTableName, null);
        
        if (columnsRs != null) {
            while (columnsRs.next()) {
                Column column = new Column();
                column.setName(columnsRs.getString("COLUMN_NAME"));
                column.setType(columnsRs.getInt("DATA_TYPE"));
                column.setTypeName(columnsRs.getString("TYPE_NAME"));
                columns.add(column);
            }
            columnsRs.close();
        }
        
        return columns;
    }
    
    /**
     * 获取实际的表名（处理大小写敏感问题）
     */
    private String getActualTableName(Connection conn, String tableName) throws SQLException {
        DatabaseMetaData metaData = conn.getMetaData();
        
        if (metaData == null) {
            return tableName; // 如果无法获取元数据，返回原表名
        }
        
        try {
            // 首先尝试原始表名
            ResultSet tables = metaData.getTables(null, null, tableName, null);
            if (tables != null && tables.next()) {
                String actualName = tables.getString("TABLE_NAME");
                tables.close();
                return actualName;
            }
            if (tables != null) {
                tables.close();
            }
            
            // 尝试全大写表名
            tables = metaData.getTables(null, null, tableName.toUpperCase(), null);
            if (tables != null && tables.next()) {
                String actualName = tables.getString("TABLE_NAME");
                tables.close();
                return actualName;
            }
            if (tables != null) {
                tables.close();
            }
            
            // 尝试全小写表名
            tables = metaData.getTables(null, null, tableName.toLowerCase(), null);
            if (tables != null && tables.next()) {
                String actualName = tables.getString("TABLE_NAME");
                tables.close();
                return actualName;
            }
            if (tables != null) {
                tables.close();
            }
        } catch (SQLException e) {
            // 如果出现异常，返回原始表名
            return tableName;
        }
        
        // 如果都没找到，返回原始表名
        return tableName;
    }
    
    /**
     * 关闭数据库连接
     */
    private void closeConnection(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                // 忽略关闭连接的异常
            }
        }
    }
    
    /**
     * 表示表中列信息的内部类
     */
    private static class Column {
        private String name;
        private int type;
        private String typeName;
        
        public String getName() {
            return name;
        }
        
        public void setName(String name) {
            this.name = name;
        }
        
        public int getType() {
            return type;
        }
        
        public void setType(int type) {
            this.type = type;
        }
        
        public String getTypeName() {
            return typeName;
        }
        
        public void setTypeName(String typeName) {
            this.typeName = typeName;
        }
    }
}